VBscript to write and update tag value in MSSQL Database table in Siemens Wincc HMI

Published on Jan07, 2025 | Category: wincc tia
Share this article:

Microsoft SQL Server (MSSQL) is a relational database management system (RDBMS). it stores and retrieves data as request. in MSSQL data organized in table each table have row and column. VBscript in wincc hmi helps to connect MSSQL data base. by using VBscript you can add and update tag value in database table. it helps to retrieves values, data logging, report generation and remote connection of hmi to MSSQL server. vbscript is very simple language to understand. siemens hmi also support vbscript so you can easily connect database with hmi.in this article have example script to add and update tag value. The VBScript function Sub write_value() performs database operations (insert or update) based on the existence of a record in a SQL database table. It is designed to work within an HMI (Human-Machine Interface) environment, interacting with a database via ODBC.

what is Microsoft SQL server Management studio

Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing, configuring, and interacting with SQL Server instances. It is a powerful tool provided by Microsoft for database administrators (DBAs), developers, and analysts to manage databases, write queries, and perform administrative tasks. SSMS is free and can be downloaded from the official Microsoft website.

img/wincc vbscript for sql/what is Microsoft SQL server Management studio.webp

how to create tag database in MSSQL

first step to create tag database to store tag value so we can read and write these tag value. a tag database an organized collection of tag values that can be easily accessed, managed, and updated. You can create database by Using the GUI (Graphical User Interface) or Using SQL Query. follow these steps to create a database in MSSQL. Using the GUI (Graphical User Interface):

  1. In the Object Explorer, expand the Server node.
  2. Right-click on the Databases folder.
  3. Select New Database from the context menu.
  4. In the New Database window:
    • Enter the Database Name (tagdatabase).
    • Review or modify settings like file locations or sizes if needed (optional).
  5. Click OK to create the database.

img/wincc vbscript for sql/how to create tag database in MSSQL.webp

how to create database Using SQL Query:

  1. Open a new query window by clicking New Query in the toolbar.
  2. Write the following SQL code: "CREATE DATABASE tagdatabase2;"
  3. Execute the query by pressing F5 or clicking the Execute button.

img/wincc vbscript for sql/how to create database Using SQL Query.webp

how to create tag table in MSSQL

A table in a database is a structured format for storing data. It organizes data into rows and columns, making it easy to manage, retrieve, and manipulate. Tables are a fundamental component of relational databases. you can create table in mssql by using GUI or SQL query

create table Using SQL Queries

  1. Open a New Query Window:
    • In SSMS, click New Query from the toolbar.
  2. Write a SQL Script:
    • Example SQL to create a Users table:
      • 
USE tagdatabase2; -- Switch to the database

CREATE TABLE tagtable (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,        -- Unique identifier
    tagname NVARCHAR(50) NOT NULL, -- Tag name
    tagvalue INT NOT NULL,         -- Tag value
    JoinDate DATE DEFAULT GETDATE() -- Default to current date
);
  3. Execute the Script:
    • Press F5 or click the Execute button to create the table.

img/wincc vbscript to read mssql tag table/create table Using SQL Queries.webp

how to configure dsn for wincc sql server

A Data Source Name (DSN) is a configuration that provides the necessary information for an application to connect to a database, such as the database driver, server address, and authentication details. DSN commonly stands for Data Source Name, a configuration used in computing to define how an application connects to a database. Step 1: Open ODBC Data Source Administrator

  1. open control panel
  2. open windows tools
  3. Choose the appropriate version of ODBC:
    • Use the 32-bit version if your application is 32-bit.
    • Use the 64-bit version if your application is 64-bit.
here we select 32 bit version for this article.

img/wincc vbscript for sql/how to configure dsn for sql server.webp

Step 2: Add a New DSN

  1. In the ODBC Data Source Administrator, go to the System DSN or User DSN tab:
    • System DSN: Available to all users on the system.
    • User DSN: Available only to the current user.
  2. Click the Add button.

img/wincc vbscript for sql/Step 2 Add a New DSN.webp

select driver for DSN

  • Select SQL Server or ODBC Driver for SQL Server from the list of drivers and click Finish.

    • img/wincc vbscript for sql/configure the dsn.webp
    Step 3: Configure the DSN
    1. In the Create a New Data Source to SQL Server window:
      • Enter a name for the DSN (e.g., MySql).
      • Provide an optional description.
      • Select the SQL Server instance to connect.
    2. Click Next.

    img/wincc vbscript for sql/enter name of DSN.webp

    Step 4: Configure Authentication

    1. Choose the authentication method:
      • Windows Authentication: Uses the current Windows user's credentials.
      • SQL Server Authentication: Requires a username and password.
    2. Click Next.

    img/wincc vbscript for sql/Configure Authentication.webp

    Step 5: Configure the Default Database

    1. Select the default database for the DSN from the dropdown menu.
    2. Click Next.

    img/wincc vbscript for sql/Configure the Default Database.webp

    Step 6: Test the DSN

    1. Click the Test Data Source button to ensure the DSN is working correctly.
    2. If the test is successful, click Finish.

    img/wincc vbscript for sql/Test the DSN.webp

    open tia portal and create a project

    open tia portal and create a project to read and update tag value.first open tag table and add a input1 tag in tag table and select integer data type.

    img/wincc vbscript update insert tagvalue to data base/open tia portal and create a project.webp

    after adding tag in tag table. open screen and then add following component in your screen

    img/wincc vbscript update insert tagvalue to data base/add following component in your screen.webp

    VBscript to write and update tag value in MSSQL Database table in Siemens Wincc HMI

    img/wincc vbscript update insert tagvalue to data base/VBscript to write and update tag value in MSSQL Database table in Siemens Wincc HMI.webp

    above is the example of VBscript to write and update tag value. The script checks if a specific record exists in the database. If it exists, the script updates the record. Otherwise, it inserts a new record. The HMI's text field displays success or error messages during execution. 

    Sub write_value()
    Defines the subroutine write_value, which performs database operations.

    Variable Declaration

    ' Variables
Dim conn, strConn, sqlCheck, sqlInsert, sqlUpdate, dsn
Dim valueToInsert
Dim textfield
Dim rs
    
    Declares variables for database connections, SQL queries, HMI fields, and recordset handling.

    HMI Text Field Reference

    Set textfield = HmiRuntime.Screens("Screen_1").ScreenItems("Text field_1")
    References the text field Text field_1 on Screen_2 in the HMI runtime for status messages.

    Define ODBC DSN and Connection String

    ' ODBC DSN details
dsn = "MySql" ' Replace with your ODBC DSN name

' Connection string using DSN and Windows Authentication
strConn = "DSN=" & dsn & ";Trusted_Connection=Yes;"
    
    Defines the ODBC DSN name and builds the connection string for Windows Authentication.

    Define Value to Insert

    valueToInsert = SmartTags("input1")
    Defines the value to be inserted or updated in the database. Replace SmartTags("input1") with the actual value source.

    SQL Check Query

    sqlCheck = "SELECT COUNT(*) AS RecordCount FROM tagtable WHERE tagname = 'temprature1'"
    Defines the SQL query to check if a record with tagname = 'temprature1' exists in the tagtable.

    Establish Database Connection

    ' Create the connection object
Set conn = CreateObject("ADODB.Connection")

' Attempt to open the connection
On Error Resume Next
conn.Open strConn
    
    Creates an ADO connection object and attempts to open the database connection.

    Check Connection Status

    If conn.State = 1 Then
    Verifies that the database connection is successfully established (state 1).

    Execute SQL Check Query

    Set rs = conn.Execute(sqlCheck)
    Executes the SQL query to check if the record exists and stores the result in rs.

    Insert or Update Logic

    If rs.Fields("RecordCount").Value > 0 Then
    ' Update existing record
    sqlUpdate = "UPDATE tagtable SET tagvalue = '" & valueToInsert & "' WHERE tagname = 'temprature1'"
    conn.Execute sqlUpdate
    textfield.Text = "Record updated successfully!"
Else
    ' Insert new record
    sqlInsert = "INSERT INTO tagtable (tagname, tagvalue) VALUES ('temprature1', '" & valueToInsert & "')"
    conn.Execute sqlInsert
    textfield.Text = "Value inserted successfully into the database!"
End If
    
    Checks if the record exists. If it does, updates the value; otherwise, inserts a new record. Displays a success message in the HMI text field.

    Handle Connection Errors

    Else
    textfield.Text = "Failed to connect to the database."
    Displays an error message in the HMI text field if the database connection fails.

    Cleanup Resources

    ' Close the connection and clean up
If conn.State = 1 Then conn.Close
Set conn = Nothing
    
    Closes the database connection and releases the connection object to free resources.

    End the Subroutine

    Recommended for You